import openpyxl
from openpyxl.styles import Font, Alignment
from openpyxl.styles import PatternFill

# 示例JSON数据
data = {"identify_results": [{"tables": [{"cells": [
    {"startrow": 0, "startcol": 0, "endrow": 1, "endcol": 1, "region": [12, 160, 99, 161, 99, 183, 12, 183],
     "lines": [{"text": "日期"}],
     "text_rects": [[{"region": [40, 163, 70, 163, 70, 179, 40, 179], "confidence": 0.999, "raw_text": "日期"}]]},
    {"startrow": 0, "startcol": 1, "endrow": 1, "endcol": 2, "region": [99, 161, 185, 161, 185, 183, 99, 183],
     "lines": [{"text": "送货单号"}], "text_rects": [
        [{"region": [121, 163, 176, 163, 176, 179, 121, 179], "confidence": 0.998225, "raw_text": "送货单号"}]]},
    {"startrow": 0, "startcol": 2, "endrow": 1, "endcol": 3, "region": [185, 161, 272, 161, 272, 183, 185, 183],
     "lines": [{"text": "货型容量"}], "text_rects": [
        [{"region": [204, 164, 256, 164, 256, 179, 204, 179], "confidence": 0.998225, "raw_text": "货型容量"}]]},
    {"startrow": 0, "startcol": 3, "endrow": 1, "endcol": 4, "region": [272, 161, 358, 161, 358, 183, 272, 183],
     "lines": [{"text": "单位"}],
     "text_rects": [[{"region": [300, 163, 331, 163, 331, 179, 300, 179], "confidence": 0.999, "raw_text": "单位"}]]},
    {"startrow": 0, "startcol": 4, "endrow": 1, "endcol": 5, "region": [358, 161, 445, 161, 445, 183, 358, 183],
     "lines": [], "text_rects": []},
    {"startrow": 0, "startcol": 5, "endrow": 1, "endcol": 6, "region": [445, 161, 532, 161, 532, 183, 445, 183],
     "lines": [{"text": "单价"}],
     "text_rects": [[{"region": [473, 163, 504, 163, 504, 179, 473, 179], "confidence": 0.999, "raw_text": "单价"}]]},
    {"startrow": 0, "startcol": 6, "endrow": 1, "endcol": 7, "region": [532, 161, 618, 161, 618, 183, 532, 183],
     "lines": [{"text": "应收金额"}], "text_rects": [[{"region": [550, 164, 602, 164, 602, 179, 550, 179],
                                                       "confidence": 0.9434750000000001, "raw_text": "应收金额"}]]},
    {"startrow": 0, "startcol": 7, "endrow": 1, "endcol": 8, "region": [618, 161, 654, 161, 654, 183, 618, 183],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 0, "endrow": 2, "endcol": 1, "region": [12, 183, 99, 183, 99, 225, 12, 224],
     "lines": [{"text": "2"}],
     "text_rects": [[{"region": [49, 194, 66, 194, 66, 211, 49, 211], "confidence": 0.999, "raw_text": "2"}]]},
    {"startrow": 1, "startcol": 1, "endrow": 2, "endcol": 2, "region": [99, 183, 185, 183, 185, 225, 99, 225],
     "lines": [{"text": "1wer"}],
     "text_rects": [[{"region": [126, 195, 165, 195, 165, 211, 126, 211], "confidence": 0.999, "raw_text": "1wer"}]]},
    {"startrow": 1, "startcol": 2, "endrow": 2, "endcol": 3, "region": [185, 183, 272, 183, 272, 225, 185, 225],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 3, "endrow": 2, "endcol": 4, "region": [272, 183, 358, 183, 358, 225, 272, 225],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 4, "endrow": 2, "endcol": 5, "region": [358, 183, 445, 183, 445, 225, 358, 225],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 5, "endrow": 2, "endcol": 6, "region": [445, 183, 532, 183, 532, 225, 445, 225],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 6, "endrow": 2, "endcol": 7, "region": [532, 183, 618, 183, 618, 225, 532, 225],
     "lines": [], "text_rects": []},
    {"startrow": 1, "startcol": 7, "endrow": 2, "endcol": 8, "region": [618, 183, 654, 183, 654, 224, 618, 225],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 0, "endrow": 3, "endcol": 1, "region": [12, 224, 99, 225, 99, 267, 12, 267],
     "lines": [{"text": "3"}],
     "text_rects": [[{"region": [49, 237, 66, 237, 66, 254, 49, 254], "confidence": 0.999, "raw_text": "3"}]]},
    {"startrow": 2, "startcol": 1, "endrow": 3, "endcol": 2, "region": [99, 225, 185, 225, 185, 267, 99, 267],
     "lines": [{"text": "Wr"}],
     "text_rects": [[{"region": [131, 237, 157, 237, 157, 253, 131, 253], "confidence": 0.99745, "raw_text": "Wr"}]]},
    {"startrow": 2, "startcol": 2, "endrow": 3, "endcol": 3, "region": [185, 225, 272, 225, 272, 267, 185, 267],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 3, "endrow": 3, "endcol": 4, "region": [272, 225, 358, 225, 358, 267, 272, 267],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 4, "endrow": 3, "endcol": 5, "region": [358, 225, 445, 225, 445, 267, 358, 267],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 5, "endrow": 3, "endcol": 6, "region": [445, 225, 532, 225, 532, 267, 445, 267],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 6, "endrow": 3, "endcol": 7, "region": [532, 225, 618, 225, 618, 267, 532, 267],
     "lines": [], "text_rects": []},
    {"startrow": 2, "startcol": 7, "endrow": 3, "endcol": 8, "region": [618, 225, 654, 224, 654, 267, 618, 267],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 0, "endrow": 4, "endcol": 1, "region": [12, 267, 99, 267, 99, 310, 12, 310],
     "lines": [{"text": "4"}],
     "text_rects": [[{"region": [49, 278, 67, 278, 67, 296, 49, 296], "confidence": 0.999, "raw_text": "4"}]]},
    {"startrow": 3, "startcol": 1, "endrow": 4, "endcol": 2, "region": [99, 267, 185, 267, 185, 310, 99, 310],
     "lines": [{"text": "Qwr"}], "text_rects": [
        [{"region": [127, 280, 162, 280, 162, 295, 127, 295], "confidence": 0.9791666666666666, "raw_text": "Qwr"}]]},
    {"startrow": 3, "startcol": 2, "endrow": 4, "endcol": 3, "region": [185, 267, 272, 267, 272, 310, 185, 310],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 3, "endrow": 4, "endcol": 4, "region": [272, 267, 358, 267, 358, 310, 272, 310],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 4, "endrow": 4, "endcol": 5, "region": [358, 267, 445, 267, 445, 310, 358, 310],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 5, "endrow": 4, "endcol": 6, "region": [445, 267, 532, 267, 532, 310, 445, 310],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 6, "endrow": 4, "endcol": 7, "region": [532, 267, 618, 267, 618, 310, 532, 310],
     "lines": [], "text_rects": []},
    {"startrow": 3, "startcol": 7, "endrow": 4, "endcol": 8, "region": [618, 267, 654, 267, 654, 310, 618, 310],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 0, "endrow": 5, "endcol": 1, "region": [12, 310, 99, 310, 99, 352, 12, 352],
     "lines": [{"text": "5"}],
     "text_rects": [[{"region": [49, 320, 66, 320, 66, 338, 49, 338], "confidence": 0.999, "raw_text": "5"}]]},
    {"startrow": 4, "startcol": 1, "endrow": 5, "endcol": 2, "region": [99, 310, 185, 310, 185, 352, 99, 352],
     "lines": [{"text": "Fds"}],
     "text_rects": [[{"region": [130, 322, 159, 322, 159, 337, 130, 337], "confidence": 0.999, "raw_text": "Fds"}]]},
    {"startrow": 4, "startcol": 2, "endrow": 5, "endcol": 3, "region": [185, 310, 272, 310, 272, 352, 185, 352],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 3, "endrow": 5, "endcol": 4, "region": [272, 310, 358, 310, 358, 352, 272, 352],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 4, "endrow": 5, "endcol": 5, "region": [358, 310, 445, 310, 445, 352, 358, 352],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 5, "endrow": 5, "endcol": 6, "region": [445, 310, 532, 310, 532, 352, 445, 352],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 6, "endrow": 5, "endcol": 7, "region": [532, 310, 618, 310, 618, 352, 532, 352],
     "lines": [], "text_rects": []},
    {"startrow": 4, "startcol": 7, "endrow": 5, "endcol": 8, "region": [618, 310, 654, 310, 654, 352, 618, 352],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 0, "endrow": 6, "endcol": 1, "region": [12, 352, 99, 352, 99, 394, 12, 394],
     "lines": [{"text": "6"}],
     "text_rects": [[{"region": [50, 363, 64, 363, 65, 380, 51, 380], "confidence": 0.999, "raw_text": "6"}]]},
    {"startrow": 5, "startcol": 1, "endrow": 6, "endcol": 2, "region": [99, 352, 185, 352, 185, 394, 99, 394],
     "lines": [{"text": "Ffff"}],
     "text_rects": [[{"region": [131, 363, 160, 363, 160, 379, 131, 379], "confidence": 0.999, "raw_text": "Ffff"}]]},
    {"startrow": 5, "startcol": 2, "endrow": 6, "endcol": 3, "region": [185, 352, 272, 352, 272, 394, 185, 394],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 3, "endrow": 6, "endcol": 4, "region": [272, 352, 358, 352, 358, 394, 272, 394],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 4, "endrow": 6, "endcol": 5, "region": [358, 352, 445, 352, 445, 394, 358, 394],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 5, "endrow": 6, "endcol": 6, "region": [445, 352, 532, 352, 532, 394, 445, 394],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 6, "endrow": 6, "endcol": 7, "region": [532, 352, 618, 352, 618, 394, 532, 394],
     "lines": [], "text_rects": []},
    {"startrow": 5, "startcol": 7, "endrow": 6, "endcol": 8, "region": [618, 352, 654, 352, 654, 394, 618, 394],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 0, "endrow": 7, "endcol": 1, "region": [12, 394, 99, 394, 99, 436, 12, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 1, "endrow": 7, "endcol": 2, "region": [99, 394, 185, 394, 185, 436, 99, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 2, "endrow": 7, "endcol": 3, "region": [185, 394, 272, 394, 272, 436, 185, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 3, "endrow": 7, "endcol": 4, "region": [272, 394, 358, 394, 357, 436, 272, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 4, "endrow": 7, "endcol": 5, "region": [358, 394, 445, 394, 445, 436, 357, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 5, "endrow": 7, "endcol": 6, "region": [445, 394, 532, 394, 532, 436, 445, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 6, "endrow": 7, "endcol": 7, "region": [532, 394, 618, 394, 618, 436, 532, 436],
     "lines": [], "text_rects": []},
    {"startrow": 6, "startcol": 7, "endrow": 7, "endcol": 8, "region": [618, 394, 654, 394, 654, 436, 618, 436],
     "lines": [], "text_rects": []}], "region": [12, 157, 658, 157, 658, 438, 12, 438], "row_num": 7, "column_num": 8}],
                              "orientation": 0, "page": 0}]}

# 创建一个新的Excel文件对象
wb = openpyxl.Workbook()

# 选择要操作的工作表
ws = wb.active

# # 合并A1到A3上下方向的单元格
# ws.merge_cells('A1:A3')
#
# # 合并B2到D2左右方向的单元格
# ws.merge_cells('B2:D2')

# 合并C3到D4的单元格（跨行跨列）
ws.merge_cells(start_row=1, start_column=1, end_row=4, end_column=4)

# 设置合并后的单元格的内容和样式
merged_cell = ws['A1']
merged_cell.value = '合并单元格示例'
merged_cell.alignment = Alignment(horizontal='center', vertical='center')

# 获取表格数据
table_data = data['identify_results'][0]['tables'][0]['cells']

# # 设置样式
# font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
alignment = Alignment(horizontal='center', vertical='center')
blue_fill = PatternFill(start_color="0000FF", end_color="0000FF", fill_type="solid")
ws['A1'].fill = blue_fill


# # 写入数据
# for cell in table_data:
#     ss = cell['lines']
#     num = 2
#     if ss:
#         cell_text = cell['lines'][0]['text']
#         start_row = cell['startrow']
#         start_col = cell['startcol']
#         ws.cell(row=start_row + num, column=start_col + num).value = cell_text
#         # ws.cell(row=start_row + num, column=start_col + num).font = font
#
#         try:
#             eval(cell_text)
#             ws.cell(row=start_row + num, column=start_col + num).alignment = alignment = Alignment(horizontal='right', vertical='center')
#         except:
#             ws.cell(row=start_row + num, column=start_col + num).alignment = alignment = alignment


# 设置列宽
ws.column_dimensions['A'].width = 20
# 保存Excel文件
wb.save('data.xlsx')
